﻿using System;
using System.Diagnostics;
using System.Collections;
using System.Data;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Excel;
namespace Maticsoft.Common
{
    /// <summary>
    /// 操作EXCEL导出数据报表的类
    /// Copyright (C) Maticsoft
    /// </summary>
    public class billDataToExcel
    {
        public billDataToExcel()
        {
        }

        #region 操作EXCEL的一个类(需要Excel.dll支持)

        private int titleColorindex = 15;
        /// <summary>
        /// 标题背景色
        /// </summary>
        public int TitleColorIndex
        {
            set { titleColorindex = value; }
            get { return titleColorindex; }
        }

        private DateTime beforeTime;			//Excel启动之前时间
        private DateTime afterTime;				//Excel启动之后时间

        #region 创建一个Excel示例
        /// <summary>
        /// 创建一个Excel示例
        /// </summary>
        public void CreateExcel()
        {
            Excel.Application excel = new Excel.Application();
            excel.Application.Workbooks.Add(true);
            excel.Cells[1, 1] = "第1行第1列";
            excel.Cells[1, 2] = "第1行第2列";
            excel.Cells[2, 1] = "第2行第1列";
            excel.Cells[2, 2] = "第2行第2列";
            excel.Cells[3, 1] = "第3行第1列";
            excel.Cells[3, 2] = "第3行第2列";

            //保存
            excel.ActiveWorkbook.SaveAs("./tt.xls", XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
            //打开显示
            excel.Visible = true;
            //			excel.Quit();
            //			excel=null;            
            //			GC.Collect();//垃圾回收
        }
        #endregion

        #region 将DataTable的数据导出显示为报表
        /// <summary>
        /// 将DataTable的数据导出显示为报表--对外——普通模式
        /// </summary>
        /// <param name="dt">要导出的数据</param>
        /// <param name="strTitle">导出报表的标题</param>
        /// <param name="FilePath">保存文件的路径</param>
        /// <returns></returns>
        public string OutputExcelo(System.Data.DataTable dt, System.Data.DataTable dt2, string strTitle, string FilePath, string[] info)
        {
            beforeTime = DateTime.Now;

            Excel.Application excel;
            Excel._Workbook xBk;
            Excel._Worksheet xSt;

            int rowIndex = 16;
            int colIndex = 0;
            int Index = 4;
            excel = new Excel.ApplicationClass();
            xBk = excel.Workbooks.Add(true);
            xSt = (Excel._Worksheet)xBk.ActiveSheet;
            //打出联系方式 
            excel.Cells[4, 1] = "TO: ";
            excel.Cells[5, 1] = "TEL: ";
            excel.Cells[6, 1] = "FAX: ";
            excel.Cells[7, 1] = "FR: ";
            excel.Cells[8, 1] = "TEL: ";
            excel.Cells[9, 1] = "FAX: ";
            excel.Cells[10, 1] = "日期: ";
            excel.Cells[11, 1] = "审核人: ";
            //范围靠右
            xSt.get_Range(excel.Cells[4, 1], excel.Cells[11, 1]).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;//设置日期型的字段格式为居中对齐
            xSt.get_Range(excel.Cells[4, 2], excel.Cells[11, 2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            //xSt.get_Range(excel.Cells[4, 1], excel.Cells[11, 2]).Font.Size = 15;
            //xSt.get_Range(excel.Cells[4, 1], excel.Cells[11, 2]).Font.Bold = true;
            foreach (string a in info)
            {
                excel.Cells[Index, 2] = a;
                Index++;
            }
            //取得列标题			
            foreach (DataColumn col in dt.Columns)
            {
                colIndex++;

                excel.Cells[16, colIndex] = col.ColumnName;

                //设置标题格式为居中对齐
                xSt.get_Range(excel.Cells[16, colIndex], excel.Cells[16, colIndex]).Font.Bold = true;
                xSt.get_Range(excel.Cells[16, colIndex], excel.Cells[16, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
                xSt.get_Range(excel.Cells[16, colIndex], excel.Cells[16, colIndex]).Font.Size = 15;
            }
            string b = "";
            foreach (DataColumn dc in dt2.Columns)
            {
                //    if (dc.DataType == System.Type.GetType("System.DateTime"))
                //        b += dc.ColumnName + ":" + (Convert.ToDateTime(dt2.Rows[0][dc].ToString()).ToString("yyyy-MM-dd")) + "，";
                //    else
                b += dc.ColumnName + ":" + dt2.Rows[0][dc].ToString() + ",";
            }
            excel.Cells[14, 1] = b;
            xSt.get_Range(excel.Cells[14, 1], excel.Cells[14, 5]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;
            xSt.get_Range(excel.Cells[14, 1], excel.Cells[14, 5]).WrapText = true;
            xSt.get_Range(excel.Cells[14, 1], excel.Cells[14, 5]).Select();
            xSt.get_Range(excel.Cells[14, 1], excel.Cells[14, 5]).Rows.AutoFit();
            xSt.get_Range(excel.Cells[14, 1], excel.Cells[14, 5]).Merge();
            //取得表格中的数据			
            foreach (DataRow row in dt.Rows)
            {
                rowIndex++;
                colIndex = 0;
                foreach (DataColumn col in dt.Columns)
                {
                    colIndex++;
                    if (col.DataType == System.Type.GetType("System.DateTime"))
                    {
                        excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
                        xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐
                    }
                    else
                        if (col.DataType == System.Type.GetType("System.String"))
                        {
                            excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
                            xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐
                        }
                        else
                        {
                            excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                        }
                }
            }


            //加载一个合计行			
            int rowSum = rowIndex + 1;
            int colSum = 2;
            excel.Cells[rowSum, 1] = "合计";
            double sum = 0.0;
            foreach (DataRow dr in dt.Rows)
            {
                sum = sum + Convert.ToDouble(dr["金额RMB"].ToString());
            }
            excel.Cells[rowSum, 3] = sum;
            xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, 2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            xSt.get_Range(excel.Cells[rowSum, 3], excel.Cells[rowSum, 3]).Font.Size = 13;

            //设置选中的部分的颜色			
            xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Select();
            //xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex =Assistant.GetConfigInt("ColorIndex");// 1;//设置为浅黄色，共计有56种


            //合并标题行
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, colIndex]).Merge();
            xSt.get_Range(excel.Cells[2, 1], excel.Cells[2, colIndex]).Merge();
            //取得整个报表的标题
            excel.Cells[2, 1] = strTitle;
            excel.Cells[1, 1] = "中国外轮报关代理";
            excel.Cells[13, 1] = "费用清单";
            //设置整个报表的标题为跨列居中			
            xSt.get_Range(excel.Cells[13, 1], excel.Cells[14, colIndex]).Select();
            xSt.get_Range(excel.Cells[13, 1], excel.Cells[14, colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;
            //基础信息
            int endIndex = rowSum + 2;
            excel.Cells[endIndex, 1] = "请将以上款项汇至我司如下账号：";
            excel.Cells[endIndex + 1, 1] = "公司名称：中国珠海外轮代理有限公司";
            excel.Cells[endIndex + 2, 1] = "开户行：中国银行珠海九洲港支行";
            excel.Cells[endIndex + 3, 1] = "账  号：704257743791";


            //设置整个报表的标题格式			
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[2, 1]).Font.Bold = true;
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[2, 1]).Font.Size = 16;

            //设置报表表格为最适应宽度
            xSt.get_Range(excel.Cells[3, 1], excel.Cells[rowSum, colIndex]).Select();
            xSt.get_Range(excel.Cells[3, 1], excel.Cells[rowSum, colIndex]).Columns.AutoFit();
            //设置整个报表的标题为跨列居中			
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[2, colIndex]).Select();
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[2, colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;

            //绘制边框			
            xSt.get_Range(excel.Cells[16, 1], excel.Cells[rowSum, colIndex]).Borders.LineStyle = 1;
            xSt.get_Range(excel.Cells[16, 1], excel.Cells[rowSum, 2]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin;//设置左边线加粗
            xSt.get_Range(excel.Cells[16, 1], excel.Cells[16, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin;//设置上边线加粗
            xSt.get_Range(excel.Cells[16, colIndex], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin;//设置右边线加粗
            xSt.get_Range(excel.Cells[rowSum, 1], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin;//设置下边线加粗
            //字体大小
            //xSt.get_Range(excel.Cells[1, 1], excel.Cells[endIndex  +5, 30]).Font.Size = 15;
            //xSt.get_Range(excel.Cells[1, 1], excel.Cells[endIndex + 5, 30]).Font.Bold = true;

            afterTime = DateTime.Now;

            //显示效果			
            //excel.Visible=true;			
            //excel.Sheets[0] = "sss";

            ClearFile(FilePath);
            string filename = DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls";
            excel.ActiveWorkbook.SaveAs(FilePath + filename, Excel.XlFileFormat.xlExcel7, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);

            //wkbNew.SaveAs strBookName;
            //excel.Save(strExcelFileName);

            #region  结束Excel进程

            //需要对Excel的DCOM对象进行配置:dcomcnfg


            //excel.Quit();
            //excel=null;            

            xBk.Close(null, null, null);
            excel.Workbooks.Close();
            excel.Quit();


            //注意：这里用到的所有Excel对象都要执行这个操作，否则结束不了Excel进程
            //			if(rng != null)
            //			{
            //				System.Runtime.InteropServices.Marshal.ReleaseComObject(rng);
            //				rng = null;
            //			}
            //			if(tb != null)
            //			{
            //				System.Runtime.InteropServices.Marshal.ReleaseComObject(tb);
            //				tb = null;
            //			}
            if (xSt != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
                xSt = null;
            }
            if (xBk != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
                xBk = null;
            }
            if (excel != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                excel = null;
            }
            GC.Collect();//垃圾回收
            #endregion

            return filename;

        }

        /// <summary>
        /// 将DataTable的数据导出显示为报表--对外__壳牌模式
        /// </summary>
        /// <param name="dt">要导出的数据</param>
        /// <param name="strTitle">导出报表的标题</param>
        /// <param name="FilePath">保存文件的路径</param>
        /// <returns></returns>
        public string OutputExcelo2(System.Data.DataTable dt, System.Data.DataTable dt2, string strTitle, string FilePath, string[] info)
        {
            beforeTime = DateTime.Now;

            Excel.Application excel;
            Excel._Workbook xBk;
            Excel._Worksheet xSt;

            int rowIndex = 16;
            int colIndex = 0;
            int Index = 4;
            excel = new Excel.ApplicationClass();
            xBk = excel.Workbooks.Add(true);
            xSt = (Excel._Worksheet)xBk.ActiveSheet;
            //打出相关信息
            excel.Cells[3, 3] = "编号： ";
            excel.Cells[4, 1] = "委托方： ";
            excel.Cells[5, 1] = "船名: ";
            excel.Cells[6, 1] = "货物:  ";
            excel.Cells[7, 1] = "数量： ";
            excel.Cells[8, 1] = "Manual Contract No.";
            excel.Cells[9, 1] = "P/O:";
            excel.Cells[10, 1] = "提单号: ";
            excel.Cells[11, 1] = "进口日期：";
            excel.Cells[12, 1] = "运抵日期：";
            excel.Cells[13, 1] = "靠泊码头：";
            excel.Cells[14, 1] = "经办人：";
            //范围靠右
            xSt.get_Range(excel.Cells[4, 1], excel.Cells[14, 1]).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;//设置日期型的字段格式为居中对齐
            xSt.get_Range(excel.Cells[4, 2], excel.Cells[14, 2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            //xSt.get_Range(excel.Cells[4, 1], excel.Cells[11, 2]).Font.Size = 15;
            //xSt.get_Range(excel.Cells[4, 1], excel.Cells[11, 2]).Font.Bold = true;
            foreach (string a in info)
            {
                excel.Cells[Index, 2] = a;
                Index++;
            }
            //取得列标题			
            foreach (DataColumn col in dt.Columns)
            {
                colIndex++;

                excel.Cells[16, colIndex] = col.ColumnName;

                //设置标题格式为居中对齐
                xSt.get_Range(excel.Cells[16, colIndex], excel.Cells[16, colIndex]).Font.Bold = true;
                xSt.get_Range(excel.Cells[16, colIndex], excel.Cells[16, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
                xSt.get_Range(excel.Cells[16, colIndex], excel.Cells[16, colIndex]).Font.Size = 15;
            }

            //取得表格中的数据			
            foreach (DataRow row in dt.Rows)
            {
                rowIndex++;
                colIndex = 0;
                foreach (DataColumn col in dt.Columns)
                {
                    colIndex++;
                    if (col.DataType == System.Type.GetType("System.DateTime"))
                    {
                        excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
                        xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐
                    }
                    else
                        if (col.DataType == System.Type.GetType("System.String"))
                        {
                            excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
                            xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐
                        }
                        else
                        {
                            excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                        }
                }
            }


            //加载一个合计行			
            int rowSum = rowIndex + 1;
            int colSum = 2;
            excel.Cells[rowSum, 1] = "合计";
            double sum = 0.0;
            foreach (DataRow dr in dt.Rows)
            {
                sum = sum + Convert.ToDouble(dr["金额RMB"].ToString());
            }
            excel.Cells[rowSum, 3] = sum;
            xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, 2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            xSt.get_Range(excel.Cells[rowSum, 3], excel.Cells[rowSum, 3]).Font.Size = 13;

            //设置选中的部分的颜色			
            xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Select();
            //xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex =Assistant.GetConfigInt("ColorIndex");// 1;//设置为浅黄色，共计有56种


            //合并标题行
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, colIndex]).Merge();
            xSt.get_Range(excel.Cells[2, 1], excel.Cells[2, colIndex]).Merge();
            //取得整个报表的标题
            excel.Cells[2, 1] = strTitle;
            excel.Cells[1, 1] = "中国外轮报关代理";

            //设置整个报表的标题为跨列居中			
            //xSt.get_Range(excel.Cells[13, 1], excel.Cells[14, colIndex]).Select();
            //xSt.get_Range(excel.Cells[13, 1], excel.Cells[14, colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;
            //基础信息
            int endIndex = rowSum + 2;
            //excel.Cells[endIndex, 1] = "请将以上款项汇至我司如下账号：";
            //excel.Cells[endIndex + 1, 1] = "公司名称：中国珠海外轮代理有限公司";
            //excel.Cells[endIndex + 2, 1] = "开户行：中国银行珠海九洲港支行";
            //excel.Cells[endIndex + 3, 1] = "账  号：704257743791";


            //设置整个报表的标题格式			
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[2, 1]).Font.Bold = true;
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[2, 1]).Font.Size = 16;

            //设置报表表格为最适应宽度
            xSt.get_Range(excel.Cells[4, 1], excel.Cells[rowSum, colIndex]).Select();
            xSt.get_Range(excel.Cells[4, 1], excel.Cells[rowSum, colIndex]).Columns.AutoFit();
            //设置整个报表的标题为跨列居中			
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[2, colIndex]).Select();
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[2, colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;

            //绘制边框			
            xSt.get_Range(excel.Cells[16, 1], excel.Cells[rowSum, colIndex]).Borders.LineStyle = 1;
            xSt.get_Range(excel.Cells[16, 1], excel.Cells[rowSum, 2]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin;//设置左边线加粗
            xSt.get_Range(excel.Cells[16, 1], excel.Cells[16, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin;//设置上边线加粗
            xSt.get_Range(excel.Cells[16, colIndex], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin;//设置右边线加粗
            xSt.get_Range(excel.Cells[rowSum, 1], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin;//设置下边线加粗
            //字体大小
            //xSt.get_Range(excel.Cells[1, 1], excel.Cells[endIndex + 5, 30]).Font.Size = 15;
            //xSt.get_Range(excel.Cells[1, 1], excel.Cells[endIndex + 5, 30]).Font.Bold = true;

            afterTime = DateTime.Now;

            //显示效果			
            //excel.Visible=true;			
            //excel.Sheets[0] = "sss";

            ClearFile(FilePath);
            string filename = DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls";
            excel.ActiveWorkbook.SaveAs(FilePath + filename, Excel.XlFileFormat.xlExcel7, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);

            //wkbNew.SaveAs strBookName;
            //excel.Save(strExcelFileName);

            #region  结束Excel进程

            //需要对Excel的DCOM对象进行配置:dcomcnfg


            //excel.Quit();
            //excel=null;            

            xBk.Close(null, null, null);
            excel.Workbooks.Close();
            excel.Quit();


            //注意：这里用到的所有Excel对象都要执行这个操作，否则结束不了Excel进程
            //			if(rng != null)
            //			{
            //				System.Runtime.InteropServices.Marshal.ReleaseComObject(rng);
            //				rng = null;
            //			}
            //			if(tb != null)
            //			{
            //				System.Runtime.InteropServices.Marshal.ReleaseComObject(tb);
            //				tb = null;
            //			}
            if (xSt != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
                xSt = null;
            }
            if (xBk != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
                xBk = null;
            }
            if (excel != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                excel = null;
            }
            GC.Collect();//垃圾回收
            #endregion

            return filename;

        }

        /// <summary>
        /// 将DataTable的数据导出显示为报表--对内部--结算单
        /// </summary>
        /// <param name="dt">要导出的数据</param>
        /// <param name="strTitle">导出报表的标题</param>
        /// <param name="FilePath">保存文件的路径</param>
        /// <returns></returns>
        public string OutputExceli(System.Data.DataTable dt, System.Data.DataTable dt2, string strTitle, string FilePath, string[] info)
        {
            beforeTime = DateTime.Now;

            Excel.Application excel;
            Excel._Workbook xBk;
            Excel._Worksheet xSt;

            int rowIndex = 16;
            int colIndex = 0;
            int Index = 4;
            excel = new Excel.ApplicationClass();
            xBk = excel.Workbooks.Add(true);
            xSt = (Excel._Worksheet)xBk.ActiveSheet;


            //范围靠右
            xSt.get_Range(excel.Cells[4, 1], excel.Cells[11, 1]).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;//设置日期型的字段格式为居中对齐
            xSt.get_Range(excel.Cells[4, 2], excel.Cells[11, 2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            //xSt.get_Range(excel.Cells[4, 1], excel.Cells[11, 2]).Font.Size = 15;
            //xSt.get_Range(excel.Cells[4, 1], excel.Cells[11, 2]).Font.Bold = true;
            foreach (string a in info)
            {
                excel.Cells[Index, 2] = a;
                Index++;
            }
            //取得列标题			
            foreach (DataColumn col in dt.Columns)
            {
                colIndex++;

                excel.Cells[16, colIndex] = col.ColumnName;

                //设置标题格式为居中对齐
                xSt.get_Range(excel.Cells[16, colIndex], excel.Cells[16, colIndex]).Font.Bold = true;
                xSt.get_Range(excel.Cells[16, colIndex], excel.Cells[16, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
                xSt.get_Range(excel.Cells[16, colIndex], excel.Cells[16, colIndex]).Font.Size = 15;
            }
            string b = "";
            foreach (DataColumn dc in dt2.Columns)
            {
                if (dc.DataType == System.Type.GetType("System.DateTime"))
                    b += dc.ColumnName + ":" + (Convert.ToDateTime(dt2.Rows[0][dc].ToString()).ToString("yyyy-MM-dd")) + "，";
                else
                    b += dc.ColumnName + ":" + dt2.Rows[0][dc].ToString() + ",";
            }
            excel.Cells[14, 1] = b;
            xSt.get_Range(excel.Cells[14, 1], excel.Cells[14, 5]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;
            xSt.get_Range(excel.Cells[14, 1], excel.Cells[14, 5]).WrapText = true;
            xSt.get_Range(excel.Cells[14, 1], excel.Cells[14, 5]).Select();
            xSt.get_Range(excel.Cells[14, 1], excel.Cells[14, 5]).Rows.AutoFit();
            xSt.get_Range(excel.Cells[14, 1], excel.Cells[14, 5]).Merge();
            //取得表格中的数据			
            foreach (DataRow row in dt.Rows)
            {
                rowIndex++;
                colIndex = 0;
                foreach (DataColumn col in dt.Columns)
                {
                    colIndex++;
                    if (col.DataType == System.Type.GetType("System.DateTime"))
                    {
                        excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
                        xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐
                    }
                    else
                        if (col.DataType == System.Type.GetType("System.String"))
                        {
                            excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
                            xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐
                        }
                        else
                        {
                            excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                        }
                }
            }


            //加载一个合计行			
            int rowSum = rowIndex + 1;
            int colSum = 2;
            excel.Cells[rowSum, 1] = "合计";
            double sum = 0.0;
            foreach (DataRow dr in dt.Rows)
            {
                sum = sum + Convert.ToDouble(dr["金额RMB"].ToString());
            }
            excel.Cells[rowSum, 3] = sum;
            xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, 2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            xSt.get_Range(excel.Cells[rowSum, 3], excel.Cells[rowSum, 3]).Font.Size = 13;

            //设置选中的部分的颜色			
            xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Select();
            //xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex =Assistant.GetConfigInt("ColorIndex");// 1;//设置为浅黄色，共计有56种


            //合并标题行
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, colIndex]).Merge();
            xSt.get_Range(excel.Cells[2, 1], excel.Cells[2, colIndex]).Merge();
            //取得整个报表的标题
            excel.Cells[2, 1] = strTitle;
            excel.Cells[1, 1] = "中国外轮报关代理";
            excel.Cells[13, 1] = "费用清单";
            //设置整个报表的标题为跨列居中			
            xSt.get_Range(excel.Cells[13, 1], excel.Cells[14, colIndex]).Select();
            xSt.get_Range(excel.Cells[13, 1], excel.Cells[14, colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;
            //基础信息
            int endIndex = rowSum + 2;
            excel.Cells[endIndex, 1] = "请将以上款项汇至我司如下账号：";
            excel.Cells[endIndex + 1, 1] = "公司名称：中国珠海外轮代理有限公司";
            excel.Cells[endIndex + 2, 1] = "开户行：中国银行珠海九洲港支行";
            excel.Cells[endIndex + 3, 1] = "账  号：704257743791";


            //设置整个报表的标题格式			
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[2, 1]).Font.Bold = true;
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[2, 1]).Font.Size = 16;

            //设置报表表格为最适应宽度
            xSt.get_Range(excel.Cells[16, 1], excel.Cells[rowSum, 4]).Select();
            xSt.get_Range(excel.Cells[16, 1], excel.Cells[rowSum, 4]).Columns.AutoFit();
            //设置整个报表的标题为跨列居中			
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[2, colIndex]).Select();
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[2, colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;

            //绘制边框			
            xSt.get_Range(excel.Cells[16, 1], excel.Cells[rowSum, colIndex]).Borders.LineStyle = 1;
            xSt.get_Range(excel.Cells[16, 1], excel.Cells[rowSum, 2]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin;//设置左边线加粗
            xSt.get_Range(excel.Cells[16, 1], excel.Cells[16, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin;//设置上边线加粗
            xSt.get_Range(excel.Cells[16, colIndex], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin;//设置右边线加粗
            xSt.get_Range(excel.Cells[rowSum, 1], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin;//设置下边线加粗
            //字体大小
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[endIndex + 5, 30]).Font.Size = 15;
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[endIndex + 5, 30]).Font.Bold = true;

            afterTime = DateTime.Now;

            //显示效果			
            //excel.Visible=true;			
            //excel.Sheets[0] = "sss";

            ClearFile(FilePath);
            string filename = DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls";
            excel.ActiveWorkbook.SaveAs(FilePath + filename, Excel.XlFileFormat.xlExcel7, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);

            //wkbNew.SaveAs strBookName;
            //excel.Save(strExcelFileName);

            #region  结束Excel进程

            //需要对Excel的DCOM对象进行配置:dcomcnfg


            //excel.Quit();
            //excel=null;            

            xBk.Close(null, null, null);
            excel.Workbooks.Close();
            excel.Quit();


            //注意：这里用到的所有Excel对象都要执行这个操作，否则结束不了Excel进程
            //			if(rng != null)
            //			{
            //				System.Runtime.InteropServices.Marshal.ReleaseComObject(rng);
            //				rng = null;
            //			}
            //			if(tb != null)
            //			{
            //				System.Runtime.InteropServices.Marshal.ReleaseComObject(tb);
            //				tb = null;
            //			}
            if (xSt != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
                xSt = null;
            }
            if (xBk != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
                xBk = null;
            }
            if (excel != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                excel = null;
            }
            GC.Collect();//垃圾回收
            #endregion

            return filename;

        }
        #endregion

        #region 将DataTable的数据导出显示为报表
        /// <summary>
        /// 导出票结单
        /// </summary>
        /// <param name="dt2">要导出的数据</param>
        /// <param name="strTitle">导出报表的标题</param>
        /// <param name="FilePath">保存文件的路径</param>
        /// <returns></returns>
        public string OutputExcelTicket(DataSet ds, string strTitle, string FilePath, string QueryText)
        {
            beforeTime = DateTime.Now;

            Excel.Application excel;
            Excel._Workbook xBk;
            Excel._Worksheet xSt;


            excel = new Excel.ApplicationClass();
            xBk = excel.Workbooks.Add(true);
            for (int i = 1; i < ds.Tables.Count; i++)//添加sheet
            {
                xBk.Sheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            }
            foreach (Excel._Worksheet ew in xBk.Sheets)//每一个ds导出一个excel
            {
                ew.Activate();
                int rowIndex = 9;
                int colIndex = 1;
                System.Data.DataTable dt2 = ds.Tables[ew.Index - 1];
                dt2.Columns.Remove(dt2.Columns[0]);
                //打出公司信息
                ew.Cells[1, 1] = "TO: " + QueryText.ToString();
                ew.Cells[2, 1] = "TEL: ";
                ew.Cells[3, 1] = "FAX: ";
                ew.Cells[4, 1] = "FR: 中国珠海外轮代理有限公司-钟慈芳";
                ew.Cells[5, 1] = "TEL: 0756-7269306";
                ew.Cells[6, 1] = "FAX: 0756-7269213";
                ew.Cells[7, 1] = "审核人: 吴杰光";
                //设置公司信息左对齐
                ew.get_Range(excel.Cells[1, 1], excel.Cells[7, 1]).Select();
                ew.get_Range(excel.Cells[1, 1], excel.Cells[7, 1]).HorizontalAlignment = XlHAlign.xlHAlignLeft;
                //加载一个合计行			
                int rowSum = rowIndex + dt2.Rows.Count+1 ;
                int colSum = 2;
                ew.Cells[rowSum, 1] = "合计";
                //算法
                decimal sum; string a;
                for (int i = 5; i < dt2.Columns.Count; i++)
                {
                    sum = 0; a = null;
                    for (int j = 0; j < dt2.Rows.Count; j++)
                    {
                        a = dt2.Rows[j][i].ToString();
                        if (a != null && a != "")
                            sum += Convert.ToDecimal(a);
                    }
                    ew.Cells[rowSum, 2] = sum;
                }
                ew.get_Range(excel.Cells[10, 2], excel.Cells[rowSum, 2]).Select();
                ew.get_Range(excel.Cells[10, 2], excel.Cells[rowSum, 2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

                //取得表格中的数据			
                foreach (DataRow row in dt2.Rows)
                {
                    rowIndex++;
                    colIndex = 1;
                    ew.Cells[rowIndex, 1] = row[4].ToString();
                    ew.Cells[rowIndex, 2] = row[5].ToString();
                }
                //取得整个报表的标题			
                ew.get_Range(excel.Cells[8, 1], excel.Cells[8, 9]).Merge();//合并单元格
                ew.Cells[8, 8] = strTitle;
                ew.Cells[8, 1] = "费用清单";


                //简要
                string brief = dt2.Rows[0][0].ToString() + "高栏办理" + dt2.Rows[0][1].ToString() + dt2.Rows[0][2].ToString() + "吨出仓，" + "报关单号：" + dt2.Rows[0][3].ToString() + "，相关费用如下：";
                ew.Cells[9, 1] = brief;
                //基础信息
                int endIndex = rowSum + 2;
                ew.Cells[endIndex, 1] = "请将以上款项汇至我司如下账号：";
                ew.Cells[endIndex + 1, 1] = "公司名称：中国珠海外轮代理有限公司";
                ew.Cells[endIndex + 2, 1] = "开户行：中国银行珠海九洲港支行";
                ew.Cells[endIndex + 3, 1] = "账  号：704257743791";
                //设置基础信息左对齐
                ew.get_Range(excel.Cells[endIndex, 1], excel.Cells[endIndex + 3, 1]).Select();
                ew.get_Range(excel.Cells[endIndex, 1], excel.Cells[endIndex + 3, 1]).HorizontalAlignment = XlHAlign.xlHAlignLeft;

                //设置整个报表的标题格式			
                ew.get_Range(excel.Cells[1, 1], excel.Cells[endIndex + 3, 9]).Font.Bold = true;
                ew.get_Range(excel.Cells[8, 1], excel.Cells[8, 9]).Font.Size = 22;

                //设置报表表格为最适应宽度			
                ew.get_Range(excel.Cells[11, 1], excel.Cells[rowSum, colIndex]).Select();
                ew.get_Range(excel.Cells[11, 1], excel.Cells[rowSum, colIndex]).Columns.AutoFit();

                //设置整个报表的标题为跨列居中			
                ew.get_Range(excel.Cells[8, 1], excel.Cells[8, colIndex]).Select();
                ew.get_Range(excel.Cells[8, 1], excel.Cells[8, colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;


            }
            afterTime = DateTime.Now;


            ClearFile(FilePath);
            string filename = DateTime.Now.ToString("yyyyMMddHHmmssff") + "票结单.xls";
            excel.ActiveWorkbook.SaveAs(FilePath + filename, Excel.XlFileFormat.xlWorkbookNormal, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);


            #region  结束Excel进程


            xBk.Close(null, null, null);
            excel.Workbooks.Close();
            excel.Quit();
            KillExcelProcess();


            //注意：这里用到的所有Excel对象都要执行这个操作，否则结束不了Excel进程

            //xSt = ew;
            //if (xSt != null)
            //{
            //    System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
            //    xSt = null;
            //}
            //if (xBk != null)
            //{
            //    System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
            //    xBk = null;
            //}
            //if (excel != null)
            //{
            //    System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
            //    excel = null;
            //}
            GC.Collect();//垃圾回收
            #endregion

            return filename;

        }


        /// <summary>
        /// 导出对账单——对外
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="strTitle"></param>
        /// <param name="FilePath"></param>
        /// <param name="info"></param>
        /// <returns></returns>
        public string OutputExcelo(System.Data.DataTable dt, string strTitle, string FilePath, string[] info)
        {
            beforeTime = DateTime.Now;

            Excel.Application excel;
            Excel._Workbook xBk;
            Excel._Worksheet xSt;

            int rowIndex = 12;
            int colIndex = 0;
            int Index = 11;
            int brow = 3;

            excel = new Excel.ApplicationClass();

            xBk = excel.Workbooks.Add(true);
            xSt = (Excel._Worksheet)xBk.ActiveSheet;
            //打出公司名称
            excel.Cells[3, 1] = "TO: ";
            excel.Cells[4, 1] = "TEL: ";
            excel.Cells[5, 1] = "FAX: ";
            excel.Cells[6, 1] = "FR:中国珠海外轮代理有限公司-邓巧联 ";
            excel.Cells[7, 1] = "TEL: 0756-7716607";
            excel.Cells[8, 1] = "FAX: 0756-7269203";
            excel.Cells[9, 1] = "日期: ";
            excel.Cells[10, 1] = "审核人: ";

            foreach (string a in info)
            {
                excel.Cells[brow, 2] = a;
                brow++;
                if (brow > 10)
                    break;
            }
            ////取得列标题			
            foreach (DataColumn col in dt.Columns)
            {
                colIndex++;

                excel.Cells[Index + 1, colIndex] = col.ColumnName;

                //设置标题格式为居中对齐
                xSt.get_Range(excel.Cells[Index, colIndex], excel.Cells[Index, colIndex]).Font.Bold = true;
                xSt.get_Range(excel.Cells[Index, colIndex], excel.Cells[Index, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
                xSt.get_Range(excel.Cells[Index, colIndex], excel.Cells[Index, colIndex]).Font.Size = 10;
            }

            //取得表格中的数据			
            foreach (DataRow row in dt.Rows)
            {
                rowIndex++;
                colIndex = 0;
                foreach (DataColumn col in dt.Columns)
                {
                    colIndex++;
                    //if (col.DataType == System.Type.GetType("System.DateTime"))
                    //{
                    //    string dateStr = row[col.ColumnName].ToString();
                    //    if (dateStr != "")
                    //    {
                    //        excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(dateStr)).ToString("yyyy-mm-dd");
                    //        xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐
                    //    }
                    //}
                    //else
                    //{
                    if (col.DataType == System.Type.GetType("System.String"))
                    {
                        excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
                        xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐
                    }
                    else
                    {
                        excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                    }
                    //  }
                }
            }


            //加载一个备注行			
            int rowSum = rowIndex + 1;

            excel.Cells[rowSum, 1] = "备注";
            //加载一个合计行
            excel.Cells[rowSum - 1, 1] = "合计";
            //算法
            decimal sum; string b;
            for (int i = 15; i < dt.Columns.Count - 1; i++)
            {
                sum = 0; b = null;
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    b = dt.Rows[j][i].ToString();
                    if (b != null && b != "")
                    {
                        sum += Convert.ToDecimal(b);
                        excel.Cells[rowSum - 1, i + 1] = sum;
                    }
                }

            }
            //算法2
            //decimal sum1; string c;
            //for (int i = 17; i < dt.Columns.Count - 1; i++)
            //{
            //    sum1 = 0; c = null;
            //    for (int j = 0; j < dt.Rows.Count; j++)
            //    {
            //        c = dt.Rows[j][i].ToString();
            //        if (c != null && c != "")
            //        {
            //            sum1 += Convert.ToDecimal(c);
                       
            //        }
            //    }
            //    excel.Cells[rowSum - 1, 17] = sum1;
            //}
               
            
            //double sum = 0.0;
            //foreach (DataRow dr in dt.Rows)
            //{
            //    sum = sum + Convert.ToDouble(dr["金额RMB"].ToString());
            //}
            //excel.Cells[rowSum, 3] = sum;
            //xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, 2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            //xSt.get_Range(excel.Cells[rowSum, 3], excel.Cells[rowSum, 3]).Font.Size = 13;

            ////设置选中的部分的颜色			
            //xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Select();
            ////xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex =Assistant.GetConfigInt("ColorIndex");// 1;//设置为浅黄色，共计有56种


            //合并标题行
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, colIndex]).Merge();
            xSt.get_Range(excel.Cells[2, 1], excel.Cells[2, colIndex]).Merge();
            //取得整个报表的标题
            excel.Cells[2, 1] = strTitle;
            excel.Cells[1, 1] = "中国外轮报关代理";

            //设置整个报表的标题为跨列居中			
            //xSt.get_Range(excel.Cells[13, 1], excel.Cells[14, colIndex]).Select();
            //xSt.get_Range(excel.Cells[13, 1], excel.Cells[14, colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;
            ////基础信息
            int endIndex = rowSum + 2;
            excel.Cells[endIndex, 1] = "请将以上款项汇至我司如下账号：";
            excel.Cells[endIndex + 1, 1] = "公司名称：中国珠海外轮代理有限公司";
            excel.Cells[endIndex + 2, 1] = "开户行：中国银行珠海九洲港支行";
            excel.Cells[endIndex + 3, 1] = "账  号：704257743791";
            excel.Cells[endIndex + 1, colIndex - 5] = "客户确认（公章）：";
            excel.Cells[endIndex + 2, colIndex - 4] = "日期：";
            excel.Cells[endIndex + 1, colIndex - 3] = "____________________";
            excel.Cells[endIndex + 2, colIndex - 3] = "____________________";

            //设置整个报表的标题格式			
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[2, 1]).Font.Bold = true;
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[2, 1]).Font.Size = 16;

            //设置报表表格为最适应宽度
            xSt.get_Range(excel.Cells[9, 1], excel.Cells[rowSum, colIndex]).Select();
            xSt.get_Range(excel.Cells[9, 1], excel.Cells[rowSum, colIndex]).Columns.AutoFit();

            //设置整个报表的标题为跨列居中			
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[2, colIndex]).Select();
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[2, colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;

            //绘制边框			
            xSt.get_Range(excel.Cells[12, 1], excel.Cells[rowSum, colIndex]).Borders.LineStyle = 1;
            xSt.get_Range(excel.Cells[12, 1], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin;//设置左边线加粗
            xSt.get_Range(excel.Cells[12, 1], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin;//设置上边线加粗
            xSt.get_Range(excel.Cells[12, 1], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin;//设置右边线加粗
            xSt.get_Range(excel.Cells[12, 1], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin;//设置下边线加粗
            //字体大小
            //xSt.get_Range(excel.Cells[1, 1], excel.Cells[endIndex + 5, 30]).Font.Size = 15;
            //   xSt.get_Range(excel.Cells[1, 1], excel.Cells[endIndex + 5, 30]).Font.Bold = true;

            afterTime = DateTime.Now;

            //显示效果			
            //excel.Visible=true;			
            //excel.Sheets[0] = "sss";

            ClearFile(FilePath);
            //设置文件名
            string start = dt.Rows[0][0].ToString();
            string over = dt.Rows[dt.Rows.Count - 2][0].ToString();
            string filename = start +"至"+over+info[0]  + "对账单.xls";
            //string filename = DateTime.Now.ToString("yyyyMMddHHmmssff") + "对账单.xls";
            excel.ActiveWorkbook.SaveAs(FilePath + filename, Excel.XlFileFormat.xlExcel7, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);

            //wkbNew.SaveAs strBookName;
            //excel.Save(strExcelFileName);

            #region  结束Excel进程

            //需要对Excel的DCOM对象进行配置:dcomcnfg


            //excel.Quit();
            //excel=null;            

            xBk.Close(null, null, null);
            excel.Workbooks.Close();
            excel.Quit();


            //注意：这里用到的所有Excel对象都要执行这个操作，否则结束不了Excel进程
            //			if(rng != null)
            //			{
            //				System.Runtime.InteropServices.Marshal.ReleaseComObject(rng);
            //				rng = null;
            //			}
            //			if(tb != null)
            //			{
            //				System.Runtime.InteropServices.Marshal.ReleaseComObject(tb);
            //				tb = null;
            //			}
            if (xSt != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
                xSt = null;
            }
            if (xBk != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
                xBk = null;
            }
            if (excel != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                excel = null;
            }
            GC.Collect();//垃圾回收
            #endregion

            return filename;

        }
        /// <summary>
        /// 导出对账单（壳牌）——对外
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="strTitle"></param>
        /// <param name="FilePath"></param>
        /// <param name="info"></param>
        /// <returns></returns>
        public string OutputExcelo_QiaoPai(System.Data.DataTable dt, string strTitle, string FilePath, string[] info)
        {
            System.Data.DataTable dt1 = new System.Data.DataTable();
            System.Data.DataTable dt2 = new System.Data.DataTable();
            beforeTime = DateTime.Now;

            Excel.Application excel;
            Excel._Workbook xBk;
            Excel._Worksheet xSt;
            int xuhao = 1;
            int rowIndex = 4;
            int colIndex = 0;
            int Index = 3;
            //int brow = 3;

            excel = new Excel.ApplicationClass();

            xBk = excel.Workbooks.Add(true);
            xSt = (Excel._Worksheet)xBk.ActiveSheet;
            //打出公司名称
            excel.Cells[3, 2] = "委托方:壳牌（中国）有限公司 ";
            //excel.Cells[4, 1] = "TEL: ";
            //excel.Cells[5, 1] = "FAX: ";
            //excel.Cells[6, 1] = "FR:中国珠海外轮代理有限公司-邓巧联 ";
            //excel.Cells[7, 1] = "TEL: 0756-7716607";
            //excel.Cells[8, 1] = "FAX: 0756-7269203";
            //excel.Cells[9, 1] = "日期: ";
            //excel.Cells[10, 1] = "审核人: ";

            //foreach (string a in info)
            //{
            //    excel.Cells[brow, 2] = a;
            //    brow++;
            //    if (brow > 10)
            //        break;
            //}
            ////取得列标题			
            foreach (DataColumn col in dt.Columns)
            {
                colIndex++;

                excel.Cells[Index + 1, colIndex+1] = col.ColumnName;

                //设置标题格式为居中对齐
                xSt.get_Range(excel.Cells[Index, colIndex+1], excel.Cells[Index, colIndex+1]).Font.Bold = true;
                xSt.get_Range(excel.Cells[Index, colIndex+1], excel.Cells[Index, colIndex+1]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
                xSt.get_Range(excel.Cells[Index, colIndex+1], excel.Cells[Index, colIndex+1]).Font.Size = 10;
            }

            //取得表格中的数据			
            foreach (DataRow row in dt.Rows)
            {
                rowIndex++;
                colIndex = 0;

                
                excel.Cells[rowIndex, 1] = xuhao.ToString();
                xuhao++;
                foreach (DataColumn col in dt.Columns)
                {
                    colIndex++;
                   
                    //if (col.DataType == System.Type.GetType("System.DateTime"))
                    //{
                    //    string dateStr = row[col.ColumnName].ToString();
                    //    if (dateStr != "")
                    //    {
                    //        excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(dateStr)).ToString("yyyy-mm-dd");
                    //        xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐
                    //    }
                    //}
                    //else
                    //{
                    if (col.DataType == System.Type.GetType("System.String"))
                    {
                        excel.Cells[rowIndex, colIndex+1] = "'" + row[col.ColumnName].ToString();
                        xSt.get_Range(excel.Cells[rowIndex, colIndex+1], excel.Cells[rowIndex, colIndex+1]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐
                    }
                    else
                    {
                        excel.Cells[rowIndex, colIndex+1] = row[col.ColumnName].ToString();
                    }
                    
                    //  }
                }
            }


            //加载一个备注行			
            int rowSum = rowIndex + 1;

            //excel.Cells[rowSum, 1] = "备注";
            //加载一个合计行
            excel.Cells[rowSum - 1, 2] = "合计";
            //算法
            decimal sum; string b;
            for (int i = 6; i < dt.Columns.Count - 3; i++)
            {
                sum = 0; b = null;
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    b = dt.Rows[j][i].ToString();
                    if (b != null && b != "")
                    {
                        sum += Convert.ToDecimal(b);
                        excel.Cells[rowSum - 1, i + 2] = sum;
                    }
                }

            }
            //算法2
            decimal sum1; string c;
            for (int i = 17; i < dt.Columns.Count - 1; i++)
            {
                sum1 = 0; c = null;
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    c = dt.Rows[j][i].ToString();
                    if (c != null && c != "")
                    {
                        sum1 += Convert.ToDecimal(c);

                    }
                }
                excel.Cells[rowSum - 1, 17] = sum1;
            }


            //double sum = 0.0;
            //foreach (DataRow dr in dt.Rows)
            //{
            //    sum = sum + Convert.ToDouble(dr["金额RMB"].ToString());
            //}
            //excel.Cells[rowSum, 3] = sum;
            //xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, 2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            //xSt.get_Range(excel.Cells[rowSum, 3], excel.Cells[rowSum, 3]).Font.Size = 13;

            ////设置选中的部分的颜色			
            //xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Select();
            ////xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex =Assistant.GetConfigInt("ColorIndex");// 1;//设置为浅黄色，共计有56种


            //合并标题行
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, colIndex]).Merge();
            xSt.get_Range(excel.Cells[2, 1], excel.Cells[2, colIndex]).Merge();
            xSt.get_Range(excel.Cells[3, 2], excel.Cells[3, 3]).Merge();
            //取得整个报表的标题
            excel.Cells[2, 1] = strTitle;
            excel.Cells[1, 1] = "中国珠海外轮代理有限公司";

            //设置整个报表的标题为跨列居中			
            //xSt.get_Range(excel.Cells[13, 1], excel.Cells[14, colIndex]).Select();
            //xSt.get_Range(excel.Cells[13, 1], excel.Cells[14, colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;
            ////基础信息
            int endIndex = rowSum + 2;
            excel.Cells[endIndex, 2] = "备注：";
            excel.Cells[endIndex + 1, 2] = "电话：";
            excel.Cells[endIndex + 1, 3] = "'0756--7269203";
            excel.Cells[endIndex + 2, 2] = "传真：";
            excel.Cells[endIndex + 2, 3] = "'0756--7269213";
            excel.Cells[endIndex + 3, 2] = "开户行：";
            excel.Cells[endIndex + 3, 3] = "中国银行珠海九洲港支行";
            excel.Cells[endIndex + 4, 2] = "账号：";
            excel.Cells[endIndex + 4, 3] = "'704257743791";
            excel.Cells[endIndex + 6, 2] = "制单：";
            excel.Cells[endIndex + 6, 3] = "钟慈芳";
            excel.Cells[endIndex + 8, 2] = "确认：";
            excel.Cells[endIndex + 10, 2] = "谢谢您的支持与合作！";
            excel.Cells[endIndex + 6, 7] = "复核：";
            excel.Cells[endIndex + 6, 11] = "制单日期：";
            excel.Cells[endIndex + 6, 12] = DateTime.Now.ToShortDateString().ToString();
            excel.Cells[endIndex + 1, colIndex ] = "总额：";
            excel.Cells[endIndex + 1, colIndex + 1] = dt.Rows[dt.Rows.Count - 1][dt.Columns.Count - 3]; ;
            //excel.Cells[endIndex + 2, colIndex - 4] = "日期：";
            //excel.Cells[endIndex + 1, colIndex - 3] = "____________________";
            //excel.Cells[endIndex + 2, colIndex - 3] = "____________________";

            //设置整个报表的标题格式			
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[2, 1]).Font.Bold = true;
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[2, 1]).Font.Size = 16;

            //设置报表表格为最适应宽度
            xSt.get_Range(excel.Cells[3, 2], excel.Cells[rowSum, colIndex]).Select();
            xSt.get_Range(excel.Cells[3, 2], excel.Cells[rowSum, colIndex]).Columns.AutoFit();

            //设置整个报表的标题为跨列居中			
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[2, colIndex]).Select();
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[2, colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;

            //绘制边框			
            xSt.get_Range(excel.Cells[4, 1], excel.Cells[rowSum, colIndex+1]).Borders.LineStyle = 1;
            xSt.get_Range(excel.Cells[4, 1], excel.Cells[rowSum, colIndex+1]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin;//设置左边线加粗
            xSt.get_Range(excel.Cells[4, 1], excel.Cells[rowSum, colIndex + 1]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin;//设置上边线加粗
            xSt.get_Range(excel.Cells[4, 1], excel.Cells[rowSum, colIndex + 1]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin;//设置右边线加粗
            xSt.get_Range(excel.Cells[4, 1], excel.Cells[rowSum, colIndex + 1]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin;//设置下边线加粗
            //字体大小
            //xSt.get_Range(excel.Cells[1, 1], excel.Cells[endIndex + 5, 30]).Font.Size = 15;
            //   xSt.get_Range(excel.Cells[1, 1], excel.Cells[endIndex + 5, 30]).Font.Bold = true;

            afterTime = DateTime.Now;

            //显示效果			
            //excel.Visible=true;			
            //excel.Sheets[0] = "sss";

            ClearFile(FilePath);
            //设置文件名
            string start = dt.Rows[0][0].ToString();
            string over = dt.Rows[dt.Rows.Count - 2][0].ToString();
            string filename = start + "至" + over + info[0] + "对账单.xls";
            //string filename = DateTime.Now.ToString("yyyyMMddHHmmssff") + "对账单.xls";
            excel.ActiveWorkbook.SaveAs(FilePath + filename, Excel.XlFileFormat.xlExcel7, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);

            //wkbNew.SaveAs strBookName;
            //excel.Save(strExcelFileName);

            #region  结束Excel进程

            //需要对Excel的DCOM对象进行配置:dcomcnfg


            //excel.Quit();
            //excel=null;            

            xBk.Close(null, null, null);
            excel.Workbooks.Close();
            excel.Quit();


            //注意：这里用到的所有Excel对象都要执行这个操作，否则结束不了Excel进程
            //			if(rng != null)
            //			{
            //				System.Runtime.InteropServices.Marshal.ReleaseComObject(rng);
            //				rng = null;
            //			}
            //			if(tb != null)
            //			{
            //				System.Runtime.InteropServices.Marshal.ReleaseComObject(tb);
            //				tb = null;
            //			}
            if (xSt != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
                xSt = null;
            }
            if (xBk != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
                xBk = null;
            }
            if (excel != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                excel = null;
            }
            GC.Collect();//垃圾回收
            #endregion

            return filename;

        }

        /// <summary>
        /// 导出对账单——内部，期结
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="strTitle"></param>
        /// <param name="FilePath"></param>
        /// <param name="info"></param>
        /// <returns></returns>
        public string OutputExceli(System.Data.DataTable dt, string strTitle, string FilePath, string[] info)
        {
            beforeTime = DateTime.Now;

            Excel.Application excel;
            Excel._Workbook xBk;
            Excel._Worksheet xSt;

            int rowIndex = 4;
            int colIndex = 0;
            int Index = 3;
            excel = new Excel.ApplicationClass();
            xBk = excel.Workbooks.Add(true);
            xSt = (Excel._Worksheet)xBk.ActiveSheet;
            //打出公司名称
            excel.Cells[3, 1] = "TO: ";
            xSt.get_Range(excel.Cells[3, 1], excel.Cells[3, 1]).HorizontalAlignment = Excel.XlVAlign.xlVAlignJustify;

            foreach (string a in info)
            {
                excel.Cells[Index, 2] = a;
                Index++;
            }
            ////取得列标题			
            foreach (DataColumn col in dt.Columns)
            {
                colIndex++;

                excel.Cells[4, colIndex] = col.ColumnName;

                //设置标题格式为居中对齐
                xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Font.Bold = true;
                xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
                xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Font.Size = 10;
            }

            //取得表格中的数据			
            foreach (DataRow row in dt.Rows)
            {
                rowIndex++;
                colIndex = 0;
                foreach (DataColumn col in dt.Columns)
                {
                    colIndex++;
                    if (col.DataType == System.Type.GetType("System.DateTime"))
                    {
                        string dateStr = row[col.ColumnName].ToString();
                        if (dateStr != "")
                        {
                            excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(dateStr)).ToString("yyyy-mm-dd");
                            xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐
                        }
                    }
                    else
                    {
                        if (col.DataType == System.Type.GetType("System.String"))
                        {
                            excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
                            xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐
                        }
                        else
                        {
                            excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                        }
                    }
                }
            }


            //加载一个合计行			
            int rowSum = rowIndex + 1;

            //excel.Cells[rowSum, 1] = "合计";
            //double sum = 0.0;
            //foreach (DataRow dr in dt.Rows)
            //{
            //    sum = sum + Convert.ToDouble(dr["金额RMB"].ToString());
            //}
            //excel.Cells[rowSum, 3] = sum;
            //xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, 2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            //xSt.get_Range(excel.Cells[rowSum, 3], excel.Cells[rowSum, 3]).Font.Size = 13;

            ////设置选中的部分的颜色			
            //xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Select();
            ////xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex =Assistant.GetConfigInt("ColorIndex");// 1;//设置为浅黄色，共计有56种


            //合并标题行
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, colIndex]).Merge();
            xSt.get_Range(excel.Cells[2, 1], excel.Cells[2, colIndex]).Merge();
            //取得整个报表的标题
            excel.Cells[2, 1] = strTitle;
            excel.Cells[1, 1] = "中国外轮报关代理";

            //设置整个报表的标题为跨列居中			
            //xSt.get_Range(excel.Cells[13, 1], excel.Cells[14, colIndex]).Select();
            //xSt.get_Range(excel.Cells[13, 1], excel.Cells[14, colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;
            ////基础信息
            int endIndex = rowSum + 2;



            //设置整个报表的标题格式			
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[2, 1]).Font.Bold = true;
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[2, 1]).Font.Size = 16;

            //设置报表表格为最适应宽度
            xSt.get_Range(excel.Cells[4, 1], excel.Cells[rowSum, colIndex]).Select();
            xSt.get_Range(excel.Cells[4, 1], excel.Cells[rowSum, colIndex]).Columns.AutoFit();

            //设置整个报表的标题为跨列居中			
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[2, colIndex]).Select();
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[2, colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;

            //绘制边框			
            xSt.get_Range(excel.Cells[4, 1], excel.Cells[rowIndex, colIndex]).Borders.LineStyle = 1;
            xSt.get_Range(excel.Cells[4, 1], excel.Cells[rowIndex, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin;//设置左边线加粗
            xSt.get_Range(excel.Cells[4, 1], excel.Cells[rowIndex, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin;//设置上边线加粗
            xSt.get_Range(excel.Cells[4, 1], excel.Cells[rowIndex, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin;//设置右边线加粗
            xSt.get_Range(excel.Cells[4, 1], excel.Cells[rowIndex, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin;//设置下边线加粗
            //字体大小
            //xSt.get_Range(excel.Cells[1, 1], excel.Cells[endIndex + 5, 30]).Font.Size = 15;
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[endIndex + 5, 30]).Font.Bold = true;

            afterTime = DateTime.Now;

            //显示效果			
            //excel.Visible=true;			
            //excel.Sheets[0] = "sss";

            ClearFile(FilePath);
            string filename = DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls";
            excel.ActiveWorkbook.SaveAs(FilePath + filename, Excel.XlFileFormat.xlExcel7, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);

            //wkbNew.SaveAs strBookName;
            //excel.Save(strExcelFileName);

            #region  结束Excel进程

            //需要对Excel的DCOM对象进行配置:dcomcnfg


            //excel.Quit();
            //excel=null;            

            xBk.Close(null, null, null);
            excel.Workbooks.Close();
            excel.Quit();


            //注意：这里用到的所有Excel对象都要执行这个操作，否则结束不了Excel进程
            //			if(rng != null)
            //			{
            //				System.Runtime.InteropServices.Marshal.ReleaseComObject(rng);
            //				rng = null;
            //			}
            //			if(tb != null)
            //			{
            //				System.Runtime.InteropServices.Marshal.ReleaseComObject(tb);
            //				tb = null;
            //			}
            if (xSt != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
                xSt = null;
            }
            if (xBk != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
                xBk = null;
            }
            if (excel != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                excel = null;
            }
            GC.Collect();//垃圾回收
            #endregion

            return filename;

        }


        /// <summary>
        /// 导出结算单——内部，票结
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="strTitle"></param>
        /// <param name="FilePath"></param>
        /// <param name="info"></param>
        /// <returns></returns>
        public string output(System.Data.DataTable dt, string strTitle, string FilePath, string[] info)
        {

            beforeTime = DateTime.Now;

            Excel.Application excel;
            Excel._Workbook xBk;
            Excel._Worksheet xSt;

            int rowIndex = 9;
            int colIndex = 0;

            excel = new Excel.ApplicationClass();
            xBk = excel.Workbooks.Add(true);
            xSt = (Excel._Worksheet)xBk.ActiveSheet;
            //打出公司名称
            excel.Cells[3, 1] = "报关客户名称: "; excel.Cells[3, 2] = info[0];
            excel.Cells[4, 1] = "挂账客户名称: "; excel.Cells[4, 2] = info[0];
            excel.Cells[5, 1] = "发票抬头名称: "; excel.Cells[5, 2] = info[0];
            excel.Cells[6, 1] = "业务发生时间: "; excel.Cells[6, 2] = info[3]; 
            
            
            excel.Cells[7, 1] = "报关单: "; excel.Cells[7, 2] = info[1];
            excel.Cells[8, 3] = "制单时间："; excel.Cells[8, 4] = info[2].ToString();

            xSt.get_Range(excel.Cells[3, 1], excel.Cells[3, 1]).HorizontalAlignment = Excel.XlVAlign.xlVAlignJustify;


            ////取得列标题			
            foreach (DataColumn col in dt.Columns)
            {
                colIndex++;

                excel.Cells[9, colIndex] = col.ColumnName;

                //设置标题格式为居中对齐
                xSt.get_Range(excel.Cells[9, colIndex], excel.Cells[9, colIndex]).Font.Bold = true;
                xSt.get_Range(excel.Cells[9, colIndex], excel.Cells[9, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
                xSt.get_Range(excel.Cells[9, colIndex], excel.Cells[9, colIndex]).Font.Size = 10;
            }

            //取得表格中的数据			
            foreach (DataRow row in dt.Rows)
            {
                rowIndex++;
                colIndex = 0;
                foreach (DataColumn col in dt.Columns)
                {
                    colIndex++;
                    if (col.DataType == System.Type.GetType("System.DateTime"))
                    {
                        string dateStr = row[col.ColumnName].ToString();
                        if (dateStr != "")
                        {
                            excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(dateStr)).ToString("yyyy-mm-dd");
                            xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐
                        }
                    }
                    else
                    {
                        if (col.DataType == System.Type.GetType("System.String"))
                        {
                            excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
                            xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐
                        }
                        else
                        {
                            excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                        }
                    }
                }
            }


            //加载一个合计行			
            int rowSum = rowIndex + 1;

            //excel.Cells[rowSum, 1] = "合计";
            //double sum = 0.0;
            //foreach (DataRow dr in dt.Rows)
            //{
            //    sum = sum + Convert.ToDouble(dr["金额RMB"].ToString());
            //}
            //excel.Cells[rowSum, 3] = sum;
            //xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, 2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            //xSt.get_Range(excel.Cells[rowSum, 3], excel.Cells[rowSum, 3]).Font.Size = 13;

            ////设置选中的部分的颜色			
            //xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Select();
            ////xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex =Assistant.GetConfigInt("ColorIndex");// 1;//设置为浅黄色，共计有56种


            //合并标题行
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, colIndex]).Merge();
            xSt.get_Range(excel.Cells[2, 1], excel.Cells[2, colIndex]).Merge();
            //取得整个报表的标题
            excel.Cells[2, 1] = strTitle;
            excel.Cells[1, 1] = "中国珠海外轮代理有限公司" + "\n\r" + "CHINA OCEAN SHIPPING AGENCY ZHUHAI";
            //excel.Cells[rowIndex + 1, 1] = "发票：";
            //设置整个报表的标题为跨列居中			
            //xSt.get_Range(excel.Cells[13, 1], excel.Cells[14, colIndex]).Select();
            //xSt.get_Range(excel.Cells[13, 1], excel.Cells[14, colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;
            ////基础信息
            int endIndex = rowSum + 2;
            excel.Cells[rowIndex + 2, 1] = "制单人：";
            excel.Cells[rowIndex + 2, 3] = "部门经理：";
            excel.Cells[rowIndex + 4, 1] = "说明：1、包干业务中的现金支付请提供发票复印件及报销时间。";
            excel.Cells[rowIndex + 5, 1] = "      2、包干业务中的转账支付请提供发票原件。";
            //设置整个报表的标题格式			
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[2, 1]).Font.Bold = true;
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[2, 1]).Font.Size = 16;

            //设置报表表格为最适应宽度
            xSt.get_Range(excel.Cells[3, 1], excel.Cells[rowSum, 5]).Select();
            xSt.get_Range(excel.Cells[3, 1], excel.Cells[rowSum, 5]).Columns.AutoFit();
            xSt.get_Range(excel.Cells[5, 4], excel.Cells[5, 4]).ColumnWidth = 12;
            //设置整个报表的标题为跨列居中			
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[2, colIndex]).Select();
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[2, colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;
            //设置单元格左对齐
            xSt.get_Range(excel.Cells[6, 2], excel.Cells[6, 2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            xSt.get_Range(excel.Cells[7, 2], excel.Cells[7, 2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            xSt.get_Range(excel.Cells[8, 4], excel.Cells[8, 4]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            //绘制边框	
            //第六行上部线
            xSt.get_Range(excel.Cells[9, 1], excel.Cells[9, colIndex]).Borders.LineStyle = 1;
            xSt.get_Range(excel.Cells[9, 1], excel.Cells[9, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin;//设置上边线加粗
            //第六行其他线
            xSt.get_Range(excel.Cells[9, 1], excel.Cells[9, colIndex]).Borders.LineStyle = 1;
            xSt.get_Range(excel.Cells[9, 1], excel.Cells[9, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin;//设置左边线加粗
            xSt.get_Range(excel.Cells[9, 1], excel.Cells[9, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin;//设置右边线加粗
            xSt.get_Range(excel.Cells[9, 1], excel.Cells[9, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin;//设置下边线加粗
            //第六行到结尾，仅右边加线
            xSt.get_Range(excel.Cells[10, 1], excel.Cells[rowIndex + 1, colIndex]).Borders.LineStyle = 1;
            xSt.get_Range(excel.Cells[10, 1], excel.Cells[rowIndex + 1, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin;//设置右边线加粗

            xSt.get_Range(excel.Cells[rowIndex + 1, 1], excel.Cells[rowIndex + 1, colIndex]).Borders.LineStyle = 1;
            xSt.get_Range(excel.Cells[rowIndex + 1, 1], excel.Cells[rowIndex + 1, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin;//设置下边线加粗
            //字体大小
            //xSt.get_Range(excel.Cells[1, 1], excel.Cells[endIndex + 5, 30]).Font.Size = 15;
            xSt.get_Range(excel.Cells[1, 1], excel.Cells[endIndex + 5, 30]).Font.Bold = true;

            afterTime = DateTime.Now;

            //显示效果			
            //excel.Visible=true;			
            //excel.Sheets[0] = "sss";

            ClearFile(FilePath);
            string filename = info[0]+info[1]+"结算单" + ".xls";
            excel.ActiveWorkbook.SaveAs(FilePath + filename, Excel.XlFileFormat.xlExcel7, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);

            //wkbNew.SaveAs strBookName;
            //excel.Save(strExcelFileName);

            #region  结束Excel进程

            //需要对Excel的DCOM对象进行配置:dcomcnfg


            //excel.Quit();
            //excel=null;            

            xBk.Close(null, null, null);
            excel.Workbooks.Close();
            excel.Quit();


            //注意：这里用到的所有Excel对象都要执行这个操作，否则结束不了Excel进程
            //			if(rng != null)
            //			{
            //				System.Runtime.InteropServices.Marshal.ReleaseComObject(rng);
            //				rng = null;
            //			}
            //			if(tb != null)
            //			{
            //				System.Runtime.InteropServices.Marshal.ReleaseComObject(tb);
            //				tb = null;
            //			}
            if (xSt != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
                xSt = null;
            }
            if (xBk != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
                xBk = null;
            }
            if (excel != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                excel = null;
            }
            GC.Collect();//垃圾回收
            #endregion

            return filename;

        }
        #region Kill Excel进程

        /// <summary>
        /// 结束Excel进程
        /// </summary>
        public void KillExcelProcess()
        {
            Process[] myProcesses;
            DateTime startTime;
            myProcesses = Process.GetProcessesByName("Excel");

            //得不到Excel进程ID，暂时只能判断进程启动时间
            foreach (Process myProcess in myProcesses)
            {
                startTime = myProcess.StartTime;

                myProcess.Kill();

            }
        }
        #endregion

        #endregion

        #region 将DataTable的数据导出显示为报表(不使用Excel对象，使用COM.Excel)

        #region 使用示例
        /*使用示例：
		 * DataSet ds=(DataSet)Session["AdBrowseHitDayList"];
			string ExcelFolder=Assistant.GetConfigString("ExcelFolder");
			string FilePath=Server.MapPath(".")+"\\"+ExcelFolder+"\\";
			
			//生成列的中文对应表
			Hashtable nameList = new Hashtable();
			nameList.Add("ADID", "广告编码");
			nameList.Add("ADName", "广告名称");
			nameList.Add("year", "年");
			nameList.Add("month", "月");
			nameList.Add("browsum", "显示数");
			nameList.Add("hitsum", "点击数");
			nameList.Add("BrowsinglIP", "独立IP显示");
			nameList.Add("HitsinglIP", "独立IP点击");
			//利用excel对象
			DataToExcel dte=new DataToExcel();
			string filename="";
			try
			{			
				if(ds.Tables[0].Rows.Count>0)
				{
					filename=dte.DataExcel(ds.Tables[0],"标题",FilePath,nameList);
				}
			}
			catch
			{
				//dte.KillExcelProcess();
			}
			
			if(filename!="")
			{
				Response.Redirect(ExcelFolder+"\\"+filename,true);
			}
		 * 
		 * */

        #endregion

        /// <summary>
        /// 将DataTable的数据导出显示为报表(不使用Excel对象)
        /// </summary>
        /// <param name="dt">数据DataTable</param>
        /// <param name="strTitle">标题</param>
        /// <param name="FilePath">生成文件的路径</param>
        /// <param name="nameList"></param>
        /// <returns></returns>
        public string DataExcel(System.Data.DataTable dt, string strTitle, string FilePath, Hashtable nameList)
        {
            COM.Excel.cExcelFile excel = new COM.Excel.cExcelFile();
            ClearFile(FilePath);
            string filename = DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls";
            excel.CreateFile(FilePath + filename);
            excel.PrintGridLines = false;

            COM.Excel.cExcelFile.MarginTypes mt1 = COM.Excel.cExcelFile.MarginTypes.xlsTopMargin;
            COM.Excel.cExcelFile.MarginTypes mt2 = COM.Excel.cExcelFile.MarginTypes.xlsLeftMargin;
            COM.Excel.cExcelFile.MarginTypes mt3 = COM.Excel.cExcelFile.MarginTypes.xlsRightMargin;
            COM.Excel.cExcelFile.MarginTypes mt4 = COM.Excel.cExcelFile.MarginTypes.xlsBottomMargin;

            double height = 1.5;
            excel.SetMargin(ref mt1, ref height);
            excel.SetMargin(ref mt2, ref height);
            excel.SetMargin(ref mt3, ref height);
            excel.SetMargin(ref mt4, ref height);

            COM.Excel.cExcelFile.FontFormatting ff = COM.Excel.cExcelFile.FontFormatting.xlsNoFormat;
            string font = "宋体";
            short fontsize = 9;
            excel.SetFont(ref font, ref fontsize, ref ff);

            byte b1 = 1,
                b2 = 12;
            short s3 = 12;
            excel.SetColumnWidth(ref b1, ref b2, ref s3);

            string header = "页眉";
            string footer = "页脚";
            excel.SetHeader(ref header);
            excel.SetFooter(ref footer);


            COM.Excel.cExcelFile.ValueTypes vt = COM.Excel.cExcelFile.ValueTypes.xlsText;
            COM.Excel.cExcelFile.CellFont cf = COM.Excel.cExcelFile.CellFont.xlsFont0;
            COM.Excel.cExcelFile.CellAlignment ca = COM.Excel.cExcelFile.CellAlignment.xlsCentreAlign;
            COM.Excel.cExcelFile.CellHiddenLocked chl = COM.Excel.cExcelFile.CellHiddenLocked.xlsNormal;

            // 报表标题
            int cellformat = 1;
            //			int rowindex = 1,colindex = 3;					
            //			object title = (object)strTitle;
            //			excel.WriteValue(ref vt, ref cf, ref ca, ref chl,ref rowindex,ref colindex,ref title,ref cellformat);

            int rowIndex = 1;//起始行
            int colIndex = 0;



            //取得列标题				
            foreach (DataColumn colhead in dt.Columns)
            {
                colIndex++;
                string name = colhead.ColumnName.Trim();
                object namestr = (object)name;
                IDictionaryEnumerator Enum = nameList.GetEnumerator();
                while (Enum.MoveNext())
                {
                    if (Enum.Key.ToString().Trim() == name)
                    {
                        namestr = Enum.Value;
                    }
                }
                excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref namestr, ref cellformat);
            }

            //取得表格中的数据			
            foreach (DataRow row in dt.Rows)
            {
                rowIndex++;
                colIndex = 0;
                foreach (DataColumn col in dt.Columns)
                {
                    colIndex++;
                    if (col.DataType == System.Type.GetType("System.DateTime"))
                    {
                        object str = (object)(Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd"); ;
                        excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat);
                    }
                    else
                    {
                        object str = (object)row[col.ColumnName].ToString();
                        excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat);
                    }
                }
            }
            int ret = excel.CloseFile();

            //			if(ret!=0)
            //			{
            //				//MessageBox.Show(this,"Error!");
            //			}
            //			else
            //			{
            //				//MessageBox.Show(this,"请打开文件c:\\test.xls!");
            //			}
            return filename;

        }

        #endregion


        #region  清理过时的Excel文件

        private void ClearFile(string FilePath)
        {
            String[] Files = System.IO.Directory.GetFiles(FilePath);
            if (Files.Length > 10)
            {
                for (int i = 0; i < 10; i++)
                {
                    try
                    {
                        System.IO.File.Delete(Files[i]);
                    }
                    catch
                    {
                    }

                }
            }
        }
        #endregion

    }
}
        #endregion